/**
 * 
 */
package co.com.sed.model.peers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import co.com.sed.model.beans.EvalEVCIndi;
import co.com.sed.model.beans.SubCompetencia;
import co.com.sed.model.beans.ResulEVCIndSubC;
import co.com.sed.model.util.Util;

/**
 * @author Mejia
 *
 */
public class ResulEVCIndSubCPeer {

	private static final String BUSCAR_RESULEVCINDSUBCS =
		"SELECT id_resul_evc_ind_subc, resultado_evc_ind_subc, evaluacion_evc_ind,subcompetencia FROM resul_evc_ind_subc ";

	private static final String BUSCAR_RESULEVCINDSUBC  =	
		"SELECT id_resul_evc_ind_subc, resultado_evc_ind_subc, evaluacion_evc_ind,subcompetencia FROM resul_evc_ind_subc "+
		"WHERE id_resul_evc_ind_subc = &id_resul_evc_ind_subc";

	private static final String INSERTAR_RESULEVCINDSUBC  =
		"INSERT INTO resul_evc_ind_subc(resultado_evc_ind_subc, evaluacion_evc_ind, subcompetencia) VALUES "+
		"(id_resul_evc_ind_subc, resultado_evc_ind_subc, evaluacion_evc_ind, subcompetencia )";

	private static final String ACTUALIZAR_RESULEVCINDSUBC =
		"UPDATE resul_evc_ind_subc "+
		"SET resultado_evc_ind_subc = &resultado_evc_ind_subc , evaluacion_evc_ind = &evaluacion_evc_ind , subcompetencia = '&subcompetencia'"+
		"WHERE id_resul_evc_ind_subc = &id_resul_evc_ind_subc";

	private static final String ELIMINAR_RESULEVCINDSUBC =
		"DELETE FROM resul_evc_ind_subc "+
		"WHERE id_resul_evc_ind_subc = &id_resul_evc_ind_subc";

	
	/**
	 * 
	 */
	
	
	/* busca en la base de datos todos los registros de la tabla "resul_evc_ind_subc"
	* 
	* @param stmt				referencia al Statement, pasado por DataManager para realizar la consulta
	* 
	* @return					lista de resul_evc_ind_subc
	* @see         				ResulEVCIndSubC, Statement
	*/
	public static ArrayList<ResulEVCIndSubC> searchAllResulEVCIndSubC(Statement stmt, String keyword) {
		ArrayList<ResulEVCIndSubC> resul_evc_ind_subcs = new ArrayList<ResulEVCIndSubC>();

		String sql = BUSCAR_RESULEVCINDSUBCS; //"SELECT id_resul_evc_ind_subc, resultado_evc_ind_subc, evaluacion_evc_ind,subcompetencia FROM resul_evc_ind_subc ";

		ResultSet rs;
		try {
			rs = stmt.executeQuery(sql);
			try {
				while (rs.next()) {
					ResulEVCIndSubC rdoevcinsubc = new ResulEVCIndSubC();

					rdoevcinsubc.setIdResulEVCIndSubC(rs.getInt("id_resul_evc_ind_subc"));
					rdoevcinsubc.setResultadoEVCIndSubC(rs.getFloat("resultado_evc_ind_subc"));
					rdoevcinsubc.setEvaluacionEVCIndi(new EvalEVCIndi(rs.getInt("evaluacion_evc_ind")));
					rdoevcinsubc.setSubCompetencia (new SubCompetencia (rs.getInt("subcompetencia")));
					
					
					
					resul_evc_ind_subcs.add(rdoevcinsubc);
				}
			} finally {
				rs.close();
			}
		} catch (SQLException e) {
			Util.print("No es posible consultar la base de datos:"
					+ e.getMessage());
			e.printStackTrace();
		}

		return resul_evc_ind_subcs;
	}
	
	/* busca en la base de datos un registro de la tabla "resul_evc_ind_subc"
	* 
	* @param stmt				referencia al Statement, pasado por DataManager para realizar la consulta
	* @param idResulEVCIndSubC		    id de la resul_evc_ind_subc que se desea cosultar
	* 
	* @return					resul_evc_ind_subc buscada
	* @see         				ResulEVCIndSubC, Statement
	*/
	public static ResulEVCIndSubC searchResulEVCIndSubC(Statement stmt, int idResulEVCIndSubC) {
		ResulEVCIndSubC resul_evc_ind_subc = new ResulEVCIndSubC();

		String sql = BUSCAR_RESULEVCINDSUBC;
		//"SELECT id_resul_evc_ind_subc, subcompetencia, rango FROM resul_evc_ind_subc "+
		//"WHERE id_resul_evc_ind_subc = &id_resul_evc_ind_subc";
		
		
		// se setean los "parametros"
		sql = sql.replaceAll("&id_resul_evc_ind_subc", String.valueOf(idResulEVCIndSubC));
		
		ResultSet rs;
		try {
			rs = stmt.executeQuery(sql);
			try {
				if (rs.next()) {
									
					resul_evc_ind_subc.setIdResulEVCIndSubC(rs.getInt("id_resul_evc_ind_subc"));
					resul_evc_ind_subc.setResultadoEVCIndSubC(rs.getFloat("resultado_evc_ind_subc"));
					resul_evc_ind_subc.setEvaluacionEVCIndi(new EvalEVCIndi(rs.getInt("evaluacion_evc_ind")));
					resul_evc_ind_subc.setSubCompetencia (new SubCompetencia (rs.getInt("subcompetencia")));
										
				}
			} finally {
				rs.close();
			}
		} catch (SQLException e) {
			Util.print("No es posible consultar la base de datos:" + e.getMessage());
			e.printStackTrace();
		}

		return resul_evc_ind_subc;
	}
	
	/* Inserta una ResulEVCIndSubC en la base de datos
	 * 
	 * @param stmt				referencia al Statement, pasado por DataManager para ejecutar la sentencia
	 * @param resul_evc_ind_subc		resul_evc_ind_subc que se va a insertar en la base de datos
	 *  
	 * @return					void
	 * @throws SQLException		si ocurre un error al insertar el registro en la base de datos
	 * 
	 * @see         			DataManager
	 */
	public static int insertarResulEVCIndSubC(Statement stmt, ResulEVCIndSubC resul_evc_ind_subc) throws SQLException {
		int idNuevaResulEVCIndSubC = -1;
		ResultSet rs = null;
		
		try {
			String sql = INSERTAR_RESULEVCINDSUBC;
			 //se setean los "parametros": &id_calificacion, &cualitativa, &cuantitativa, &descripcion 
			sql = sql.replaceAll("&resultado_evc_ind_subc", "" + resul_evc_ind_subc.getResultadoEVCIndSubC())
					 .replaceAll("&evaluacion_evc_ind", "" + resul_evc_ind_subc.getEvaluacionEVCIndi())
			         .replaceAll("&id_eval_evc_indiv", "" + resul_evc_ind_subc.getSubCompetencia());
			
			stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
			
			rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				idNuevaResulEVCIndSubC = rs.getInt("id_resul_evc_ind_subc");
				Util.print("nuevo registro: " + idNuevaResulEVCIndSubC);
			}
		} finally {
			rs.close();
		}
		
		return idNuevaResulEVCIndSubC;
	}
		
	

	/* Actualiza una resul_evc_ind_subc en la base de datos
	 * 
	 * @param stmt				referencia al Statement, pasado por DataManager para ejecutar la sentencia
	 * @param resul_evc_ind_subc	resul_evc_ind_subc que se va a actualizar en la base de datos
	 * 
	 * @return					void
	 * @throws SQLException		si ocurre un error al actualizar el registro en la base de datos
	 * 
	 * @see         			DataManager
	 */
	public static void actualizarResulEVCIndSubC(Statement stmt, ResulEVCIndSubC resul_evc_ind_subc) throws SQLException {
		String sql = ACTUALIZAR_RESULEVCINDSUBC;
		 //se setean los "parametros": &id_resul_evc_ind_subc, &subcompetencia
		sql = sql.replaceAll("&id_resul_evc_ind_subc", "" + resul_evc_ind_subc.getIdResulEVCIndSubC())
				 .replaceAll("&resultado_evc_ind_subc", "" + resul_evc_ind_subc.getResultadoEVCIndSubC())
				 .replaceAll("&evaluacion_evc_ind", "" + resul_evc_ind_subc.getEvaluacionEVCIndi())
		         .replaceAll("&id_eval_evc_indiv", "" + resul_evc_ind_subc.getSubCompetencia());
		
		stmt.executeUpdate(sql);
	}
	
	public static void eliminarResulEVCIndSubC(Statement stmt, ResulEVCIndSubC resul_evc_ind_subc) throws SQLException {
		String sql = ELIMINAR_RESULEVCINDSUBC;
		// se setean los "id_resul_evc_ind_subc = &id_resul_evc_ind_subc";
		sql = sql.replaceAll("&id_resul_evc_ind_subc", "" + resul_evc_ind_subc.getIdResulEVCIndSubC());

		stmt.executeUpdate(sql);
	}
	
}
